In [ ]:
import pandas as pd
import plotly.express as px
from post_roe.query import TinyQuery as tq

Question: How is ADI correlated to Total Loss (TL)?¶

  • TL = fn(state_status, distance, etc)
In [ ]:
def _total_loss(state_status):
    if state_status == 'at_risk':
        return 1
    else:
        return 0

adi = tq.adi_with_status_query()
adi['_loss'] = adi['state_status'].apply(_total_loss)
adi
Out[ ]:
state zip5 count min max adi_mean adi_median std lat lon population state_status _status_wp _adi_decile _loss
0 AK 99501 2988 7.0 97.0 33.152276 36.0 18.076871 61.219998 -149.857840 17603 protected Legal and likely to be protected 3 0
1 AK 99502 2745 11.0 43.0 24.122404 25.0 8.677508 61.163652 -149.996643 24168 protected Legal and likely to be protected 2 0
2 AK 99503 2524 10.0 72.0 39.593502 41.0 8.925617 62.043689 -158.174466 14563 protected Legal and likely to be protected 4 0
3 AK 99504 3420 19.0 98.0 36.341228 31.0 16.996213 61.190578 -149.607154 40914 protected Legal and likely to be protected 3 0
4 AK 99505 1020 31.0 31.0 31.000000 31.0 0.000000 61.284745 -149.653973 6174 protected Legal and likely to be protected 3 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
31525 WY 83124 267 38.0 74.0 48.921348 38.0 16.580783 41.758808 -110.315880 137 at_risk Ban within 30 days 3 1
31526 WY 83126 281 33.0 33.0 33.000000 33.0 0.000000 42.581108 -110.904209 334 at_risk Ban within 30 days 3 1
31527 WY 83127 1689 27.0 39.0 36.278863 39.0 4.083667 42.918972 -110.997753 3041 at_risk Ban within 30 days 3 1
31528 WY 83128 892 23.0 44.0 42.233184 44.0 5.224854 43.040079 -110.722208 1601 at_risk Ban within 30 days 4 1
31529 WY 83414 111 3.0 22.0 3.171171 3.0 1.803400 43.861439 -110.935725 544 at_risk Ban within 30 days 0 1

31400 rows × 15 columns

In [ ]:
adi_decile = adi.groupby(['_loss','_adi_decile']).agg(population=("population","sum")).reset_index()

px.bar(
    adi_decile, 
    x='_adi_decile', 
    y="population", 
    facet_col='_loss',
    title="Population by ADI Decile Distribution | Protected vs At-Risk States",
    height=400
).show(renderer='notebook')

adi_decile
Out[ ]:
_loss _adi_decile population
0 0 0 20547321
1 0 1 25770448
2 0 2 22733956
3 0 3 16789534
4 0 4 13838009
5 0 5 9901878
6 0 6 7860189
7 0 7 6219300
8 0 8 4234561
9 0 9 2306551
10 1 0 1932551
11 1 1 6664790
12 1 2 10858248
13 1 3 17221953
14 1 4 21195259
15 1 5 25933649
16 1 6 27622211
17 1 7 27223894
18 1 8 23993984
19 1 9 15199419
In [ ]:
px.density_heatmap(adi, y='state_status', x='_adi_decile', z='population').show(renderer="notebook")